package com.carmaintain.db;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import com.carmaintain.common.Constants;
import com.carmaintain.common.Utils;
import com.carmaintain.config.Configs;
import com.carmaintain.models.Merchant;

public class UserLib {
	/**
	 * 商家登录
	 * 
	 * @param String	username	用户名
	 * @param String	password	密码
	 * @return 用户名及用户代码
	 */
	public static Map<String, String> login(String userid, String password) throws Exception{
		Map<String, String> user = null;
		ResultSet result = null;
		
		String sql = "" +
				" SELECT " +
				"	user_id " + 
				" FROM " + Constants.TABLE_MERCHANT + 
				" WHERE " +
				"	user_id = '" + StringEscapeUtils.escapeSql(userid) + "' " +
				" AND password = '" + StringEscapeUtils.escapeSql(Utils.md5(password)) + "' ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			user = new HashMap<String, String>();
			String userCode = Utils.md5(result.getString("user_id") + System.currentTimeMillis());
			user.put("userid", result.getString("user_id"));
			user.put("usercode", userCode);
			
			//删除数据库中过期的用户代码
			sql = "" +
					" DELETE FROM " + Constants.TABLE_USER_CODE + 
					" WHERE limit_time < " + System.currentTimeMillis();
			DBConn.update(sql);
			
			//插入用户代码
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_USER_CODE + 
					"	(user_id, user_code, user_type, limit_time) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(result.getString("user_id")) + "', " +
					"	'" + StringEscapeUtils.escapeSql(userCode) + "', " + 
					Constants.USER_TYPE_MERCHANT + ", " + 
					(System.currentTimeMillis() + 24 * 60 * 60 * 1000) +")";
			DBConn.update(sql);
		}
		
		return user;
	}
	
	/**
	 * 客户登录
	 * 
	 * @param String	customerId	用户ID
	 * @param String	password	密码
	 * @return 用户名及用户代码
	 */
	public static Map<String, String> customerLogin(String customerId, String password) throws Exception{
		Map<String, String> customer = null;
		ResultSet result = null;
		
		String sql = "" +
				" SELECT " +
				"	user_id " + 
				" FROM " + Constants.TABLE_CUSTOMER + 
				" WHERE " +
				"	user_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
				" AND password = '" + StringEscapeUtils.escapeSql(Utils.md5(password)) + "' ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			customer = new HashMap<String, String>();
			String userCode = Utils.md5(result.getString("user_id") + System.currentTimeMillis());
			customer.put("userid", result.getString("user_id"));
			customer.put("usercode", userCode);
			
			//删除数据库中过期的用户代码
			sql = "" +
					" DELETE FROM " + Constants.TABLE_USER_CODE + 
					" WHERE limit_time < " + System.currentTimeMillis();
			DBConn.update(sql);
			
			//插入用户代码
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_USER_CODE + 
					"	(user_id, user_code, user_type, limit_time) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(result.getString("user_id")) + "', " +
					"	'" + StringEscapeUtils.escapeSql(userCode) + "', " + 
					Constants.USER_TYPE_CUSTOMER + ", " + 
					(System.currentTimeMillis() + 24 * 60 * 60 * 1000) +")";
			DBConn.update(sql);
		}
		
		return customer;
	}
	
	/**
	 * 获取商家信息
	 * @return 商家信息
	 */
	public static Merchant getProfile(String merchantId) throws Exception{
		Merchant user = null;
		ResultSet result = null;
		List<String> photos = null;
		
		String sql = "" +
				" SELECT " +
				"	user_id, " +
				"	brand_name, " +
				"	corporation_name, " +
				"	email, " +
				"	office_phone, " +
				"	mobile, " +
				"	introduction, " +
				"	authentication_flg " + 
				" FROM " + Constants.TABLE_MERCHANT + 
				" WHERE " +
				"	user_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			user = new Merchant();

			user.setUserid(result.getString("user_id"));
			user.setBrandName(result.getString("brand_name"));
			user.setCorporationName(result.getString("corporation_name"));
			user.setEmail(result.getString("email"));
			user.setOfficeTel(result.getString("office_phone"));
			user.setMobile(result.getString("mobile"));
			user.setIntroduction(result.getString("introduction"));
			user.setAuthenticationFlg(result.getString("authentication_flg"));
		}
		
		sql = "" +
				" SELECT " +
				"	image_name " + 
				" FROM " + Constants.TABLE_MERCHANT_PHOTO + 
				" WHERE " +
				"	merchant_id = '" + StringEscapeUtils.escapeSql(merchantId) + "' ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			photos = new ArrayList<String>();
			do {
				photos.add(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("image_name"));
			} while (result.next());
			
			user.setPhotos(photos);
		}
		
		return user;
	}
	
	/**
	 * 检查用户登录
	 * @return  用户ID:	已登录
	 * 			null:	未登录
	 */
	public static String  checkUserLogin(String userid, String userCode, int userType) throws Exception{
		if (userid == null || "".equals(userid.trim()) || userCode == null || "".equals(userCode.trim()))
		{
			return null;
		}
		
		String sql="" +
				" SELECT " +
				" 	user_id " +
				" FROM " + Constants.TABLE_USER_CODE +
				" WHERE user_id = '" + StringEscapeUtils.escapeSql(userid) + "'" +
				" AND user_code = '" + StringEscapeUtils.escapeSql(userCode) + "'" +
				" AND user_type = '" + userType + "'" +
				" AND limit_time > " + System.currentTimeMillis();
		ResultSet result = DBConn.query(sql);
		if (result.first()){
			return result.getString("user_id");
		}else{
			return null;
		}
	}
	
	/**
	 * 判断此用户名是否已存在
	 * @return true or false
	 */
	public static boolean checkUserNameExist(String userid, int userType) throws Exception {

		String sql = "";
		if (Constants.USER_TYPE_MERCHANT == userType) {
			sql = " SELECT user_id " +
				" FROM " + Constants.TABLE_MERCHANT +
				" WHERE user_id='" + StringEscapeUtils.escapeSql(userid) + "'";
		} else {
			sql = " SELECT user_id " +
					" FROM " + Constants.TABLE_CUSTOMER +
					" WHERE user_id='" + StringEscapeUtils.escapeSql(userid) + "'";
		}
		ResultSet result = DBConn.query(sql);
		if(result.first()){
			//用户名已存在
			return true;
		}else{
			//用户名可用
			return false;
		}
	}
	
	/**
	 * 申请认证
	 * @return true or false
	 */
	public static boolean applyAuth(String merchantId) throws Exception {
		String sql="" +
				" UPDATE " + Constants.TABLE_MERCHANT +
				" SET authentication_flg = " + Constants.MERCHANT_AUTHED +
				" WHERE user_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'";
		DBConn.update(sql);

		return true;
	}
	

	/**
	 * 判断车牌是否可用
	 * @return true or false
	 */
	public static boolean checkLicenseNumber(String license) throws Exception {
		String sql="" +
				" SELECT license_number " +
				" FROM " + Constants.TABLE_CAR_INFO +
				" WHERE license_number = '" + StringEscapeUtils.escapeSql(license) + "'";
		ResultSet result = DBConn.query(sql);
		if(result.first()){
			//车牌已存在
			return false;
		}else{
			//车牌可用
			return true;
		}
	}

	/**
	 * 判断此邮箱是否已存在
	 * @return true or false
	 */
	public static boolean checkEmailExist(String email, int userType) throws Exception {
		String sql = "";
		if (Constants.USER_TYPE_MERCHANT == userType) {
			sql =" SELECT email FROM " + Constants.TABLE_MERCHANT +
					" WHERE email='" + StringEscapeUtils.escapeSql(email) + "'";
		} else {
			sql =" SELECT email FROM " + Constants.TABLE_CUSTOMER +
					" WHERE email='" + StringEscapeUtils.escapeSql(email) + "'";
		}
		ResultSet result = DBConn.query(sql);
		if(result.first()){
			//邮箱已存在
			return true;
		}else{
			//邮箱可用
			return false;
		}
	}

	/**
	 * 商家注册
	 */
	public static Map<String, String> register(String merchantId, String brandName, String corporationName,
			String password, String email, String officephone, String mobile, String[] images, String introduction) throws Exception {
		Map<String, String> ret = null;
		String sql = "" +
				" INSERT INTO " + Constants.TABLE_MERCHANT +
				" (user_id, brand_name, corporation_name, password, email, office_phone, mobile, introduction)" +
				" VALUES" +
				" (" +
				"'" + StringEscapeUtils.escapeSql(merchantId) + "'," +
				"'" + StringEscapeUtils.escapeSql(brandName) + "'," +
				"'" + StringEscapeUtils.escapeSql(corporationName) + "'," +
				"'" + StringEscapeUtils.escapeSql(Utils.md5(password)) + "'," +
				"'" + StringEscapeUtils.escapeSql(email) + "'," +
				"'" + StringEscapeUtils.escapeSql(officephone) + "'," +
				"'" + StringEscapeUtils.escapeSql(mobile) + "'," +
				"'" + StringEscapeUtils.escapeSql(introduction) + "')"; 
		int result=0;
		result=DBConn.update(sql);
		if (result > 0) {
			ret = new HashMap<String, String>();
			String userCode = Utils.md5(merchantId + System.currentTimeMillis());
			ret.put("userid", merchantId);
			ret.put("usercode", userCode);
			
			if (images != null && images.length > 0) {
				//插入新图片
				for (int i = 0; i < images.length; i++) {
					sql = "" +
							" INSERT INTO " + Constants.TABLE_MERCHANT_PHOTO +
							" 	(merchant_id, image_name)" +
							" VALUES (" +
							" 	'" + StringEscapeUtils.escapeSql(merchantId) + "', " +
							"	'" + StringEscapeUtils.escapeSql(images[i]) + "')";
					
					DBConn.update(sql);
				}
			}
			
			sql = "" +
					" DELETE FROM " + Constants.TABLE_USER_CODE + 
					" WHERE limit_time < " + System.currentTimeMillis();
			DBConn.update(sql);
			
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_USER_CODE + 
					"	(user_id, user_code, user_type, limit_time) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(merchantId) + "', " +
					"	'" + StringEscapeUtils.escapeSql(userCode) + "', " + 
					Constants.USER_TYPE_MERCHANT + ", " + 
					(System.currentTimeMillis() + 24 * 60 * 60 * 1000) +")";
			DBConn.update(sql);
			
			return ret;
		}else{
			return null;
		}
		
	}
	
	/**
	 * 客户注册
	 */
	public static Map<String, String> customerRegister(String customerId, String password, 
			String email, String city) throws Exception {
		Map<String, String> ret = null;
		String sql = "" +
				" INSERT INTO " + Constants.TABLE_CUSTOMER +
				" (user_id, password, email, city)" +
				" VALUES" +
				" (" +
				"'" + StringEscapeUtils.escapeSql(customerId) + "'," +
				"'" + StringEscapeUtils.escapeSql(Utils.md5(password)) + "'," +
				"'" + StringEscapeUtils.escapeSql(email) +  "', " +
				"'" + StringEscapeUtils.escapeSql(city) +  "')"; 
		
		int result = DBConn.update(sql);
		if (result > 0) {
			ret = new HashMap<String, String>();
			String userCode = Utils.md5(customerId + System.currentTimeMillis());
			ret.put("userid", customerId);
			ret.put("usercode", userCode);
			
			sql = "" +
					" DELETE FROM " + Constants.TABLE_USER_CODE + 
					" WHERE limit_time < " + System.currentTimeMillis();
			DBConn.update(sql);
			
			sql = "" +
					" INSERT INTO" +
					"	" + Constants.TABLE_USER_CODE + 
					"	(user_id, user_code, user_type, limit_time) " +
					" VALUES " +
					"	('" + StringEscapeUtils.escapeSql(customerId) + "', " +
					"	'" + StringEscapeUtils.escapeSql(userCode) + "', " + 
					Constants.USER_TYPE_CUSTOMER + ", " + 
					(System.currentTimeMillis() + 24 * 60 * 60 * 1000) +")";
			DBConn.update(sql);
			
			return ret;
		}else{
			return null;
		}
		
	}

	/**
	 * 更新用户密码
	 * @param int userId
	 * @param Merchant user
	 * @return boolean
	 */
	public static boolean updateUserPassword(String userid, String password) throws Exception {
		int result=0;
		String sql="" +
				" UPDATE " + Constants.TABLE_MERCHANT +
				" SET " +
				" 	password ='" + StringEscapeUtils.escapeSql(password) + "'"+
				" WHERE " +
				"	user_id = '" + StringEscapeUtils.escapeSql(userid) + "'";
		DBConn.update(sql);
		result=DBConn.update(sql);
		if (result > 0) {
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 新用户注册
	 */
	public static boolean updateMerchantBasicInfo(String merchantId, String brandName, String corporationName,
			String officephone, String mobile, String[] images, String introduction) throws Exception {
		Map<String, String> ret = null;
		String sql = "" +
				" UPDATE " + Constants.TABLE_MERCHANT +
				" SET brand_name = '" + StringEscapeUtils.escapeSql(brandName) + "'," +
				" 	corporation_name = '" + StringEscapeUtils.escapeSql(corporationName) + "'," +
				" 	office_phone = '" + StringEscapeUtils.escapeSql(officephone) + "'," +
				" 	mobile = '" + StringEscapeUtils.escapeSql(mobile) + "'," +
				" 	introduction = '" + StringEscapeUtils.escapeSql(introduction) + "' " +
				" WHERE user_id = '" + StringEscapeUtils.escapeSql(merchantId) + "'";

		int result=DBConn.update(sql);
		if (result > 0) {
			ret = new HashMap<String, String>();
			String userCode = Utils.md5(merchantId + System.currentTimeMillis());
			ret.put("userid", merchantId);
			ret.put("usercode", userCode);
			
			if (images != null && images.length > 0) {
				//删除旧图片
				sql = "" +
						" DELETE FROM " + Constants.TABLE_MERCHANT_PHOTO +
						" WHERE merchant_id = '" +StringEscapeUtils.escapeSql(merchantId) + "'";
				DBConn.update(sql);
				
				//插入新图片
				for (int i = 0; i < images.length; i++) {
					sql = "" +
							" INSERT INTO " + Constants.TABLE_MERCHANT_PHOTO +
							" 	(merchant_id, image_name)" +
							" VALUES (" +
							" 	'" + StringEscapeUtils.escapeSql(merchantId) + "', " +
							"	'" + StringEscapeUtils.escapeSql(images[i]) + "')";
					
					DBConn.update(sql);
				}
			}
			
			return true;
		}else{
			return false;
		}
		
	}

	/**
	 * 取得原始密码
	 * @param int userId
	 * @return String
	 */
	public static String getOldUserPassword(String userid) throws Exception{
		String sql="" +
				" SELECT " +
				" 	password " +
				" FROM " +
					Constants.TABLE_MERCHANT +
				" WHERE " +
				" 	user_id = '" + StringEscapeUtils.escapeSql(userid) + "'";
		ResultSet result = DBConn.query(sql);
		if (result.first()){
			String password=result.getString("password");
			return password;
		}else{
			return "";
		}
	}
}
